package supernova

import (
	"bytes"
	"errors"
	"fmt"
	"regexp"
	"runtime/debug"
	"strings"

	"gddgame.cc/galaxy/utils"
	"gddgame.cc/galaxy/utils/orm"
	_ "github.com/go-sql-driver/mysql"
)

const (
	UnsignedFlag  ColumnFlag = 1 << 0
	ZerofillFlag  ColumnFlag = 1 << 1
	AllowNullFlag ColumnFlag = 1 << 2
	AutoFlag      ColumnFlag = 1 << 3
)

type mysqlLogic struct {
	orm.Conn

	event      utils.Event
	isRun      bool
	engine     *Engine
	tx         orm.Transaction
	preference *Preference
}

var (
	DefaultMysql = map[string]string{
		"ENGINE":  "InnoDB",
		"CHARSET": "utf8",
	}
)

func (mysql *mysqlLogic) init(engine *Engine) {
	for key, value := range DefaultMysql {
		engine.Set(key, value)
	}
	mysql.engine = engine
	mysql.Conn = engine.executor.Get()
	mysql.event = utils.NewEvent()
	mysql.event.On(AddDatabaseOperation, mysql.AddDatabase)
	mysql.event.On(RemoveDatabaseOperation, mysql.RemoveDatabase)
	mysql.event.On(AddTableOperation, mysql.AddTable)
	mysql.event.On(RemoveTableOperation, mysql.RemoveTable)
	mysql.event.On(AddColumnOperation, mysql.AddColumn)
	mysql.event.On(RemoveColumnOperation, mysql.RemoveColumn)
	mysql.event.On(ChangeColumnOperation, mysql.ChangeColumn)
	mysql.event.On(AddIndexOperation, mysql.AddIndex)
	mysql.event.On(RemoveIndexOperation, mysql.RemoveIndex)
	mysql.event.On(AddForeignOperation, mysql.AddForeign)
	mysql.event.On(RemoveForeignOperation, mysql.RemoveForeign)
	mysql.event.On(AddRecordsOperation, mysql.AddRecords)
	mysql.event.On(ClearRecordsOperation, mysql.ClearRecords)
	mysql.event.On(AllRecordsOperation, mysql.AllRecords)
	mysql.event.On(ReplaceRecordsOperation, mysql.ReplaceRecords)
}

// GetTableNames table names
func (mysql *mysqlLogic) GetTableNames(prefix string) ([]string, error) {
	rs, err := mysql.Query("show table status")
	if err != nil {
		return nil, errors.New("show tables failed:" + err.Error())
	}
	defer rs.Close()

	var tables []string
	columns, _ := rs.Columns()
	for rs.Next() {
		var values = make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range columns {
			valuePtrs[i] = &values[i]
		}
		if err := rs.Scan(valuePtrs...); err != nil {
			return nil, errors.New("show tables failed when scan," + err.Error())
		}
		var valObj = make(map[string]interface{})
		for i, col := range columns {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				v = string(b)
			} else {
				v = val
			}
			valObj[col] = v
		}
		if valObj["Engine"] != nil {
			name := valObj["Name"].(string)
			if prefix != "" && !strings.HasPrefix(name, prefix) {
				continue
			}
			tables = append(tables, name)
		}
	}
	return tables, nil
}

// GetTableSchema table schema
func (mysql *mysqlLogic) GetTableSchema(name string) (*Table, error) {
	rs, err := mysql.Query(fmt.Sprintf("show create table `%s`", name))
	if err != nil {
		return nil, err
	}
	defer rs.Close()
	schema := ""
	for rs.Next() {
		var vname string
		if err := rs.Scan(&vname, &schema); err != nil {
			return nil, errors.New(fmt.Sprintf("get table %s 's schema failed, %s", name, err))
		}
	}
	table := parseSchema(schema)
	table.Name = name
	return table, nil
}
func (mysql *mysqlLogic) Start(preference *Preference) error {
	if mysql.isRun {
		return errors.New("operate is running")
	}
	tx, err := mysql.engine.executor.Begin()
	if err != nil {
		return err
	}
	mysql.tx = tx
	mysql.Conn = tx
	mysql.preference = preference
	mysql.isRun = true
	return nil
}
func (mysql *mysqlLogic) Cancel() error {
	if !mysql.isRun {
		return errors.New("operate is stop")
	}
	tx := mysql.tx
	mysql.tx = nil
	mysql.Conn = mysql.engine.executor.Get()
	if err := tx.Rollback(); err != nil {
		_ = tx.Rollback()
		return err
	}
	mysql.isRun = false
	return nil
}
func (mysql *mysqlLogic) Finish() error {
	if !mysql.isRun {
		return errors.New("operate is stop")
	}
	tx := mysql.tx
	mysql.tx = nil
	mysql.Conn = mysql.engine.executor.Get()
	if err := tx.Commit(); err != nil {
		_ = tx.Rollback()
		return err
	}
	mysql.isRun = false
	return nil
}

func (mysql *mysqlLogic) QueryOperation(operation Operation) (interface{}, error) {
	//fmt.Println("query", operation.Type, operation.Params)
	vs, err := mysql.event.FireInterface(operation.Type, operation.Params)
	if err != nil {
		return nil, err
	} else if len(vs) > 0 {
		return vs[0], err
	}
	return nil, nil
}
func (mysql *mysqlLogic) ExecOperation(operation Operation) error {
	//fmt.Println("exec", operation.Type, operation.Params)
	_, err := mysql.event.FireInterface(operation.Type, operation.Params)
	if err != nil {
		fmt.Println("operation", operation.Type, operation.Params)
		debug.PrintStack()
		return err
	}
	return nil
}

func (mysql *mysqlLogic) AddDatabase(name string) error {
	sql := "CREATE DATABASE `" + name + "`"
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) RemoveDatabase(name string) error {
	sql := "DROP DATABASE `" + name + "`"
	_, err := mysql.Exec(sql)
	return err
}
func (mysql *mysqlLogic) AddTable(table *Table) error {
	sql := "CREATE TABLE IF NOT EXISTS `" + table.Name + "`(\n"
	for _, column := range table.Columns {
		sql += "`" + column.Field + "` " + string(column.Type)
		if column.Length > 0 {
			sql += "(" + utils.ToString(column.Length) + ")"
		}
		if column.Flag&UnsignedFlag > 0 {
			sql += " UNSIGNED"
		}
		if column.Flag&ZerofillFlag > 0 {
			sql += " zerofill"
		}
		if column.Flag&AllowNullFlag > 0 {
			sql += " NULL"
		} else {
			sql += " NOT NULL"
		}
		if column.Flag&AutoFlag > 0 {
			sql += " AUTO_INCREMENT"
		}
		if column.Default != "" {
			sql += " DEFAULT " + column.Default + ""
		}
		if column.Comment != "" {
			sql += " COMMENT " + column.Comment + ""
		}
		sql += ",\n"
	}
	for _, index := range table.Indexs {
		switch index.Type {
		case PrimaryIndex:
			sql += "PRIMARY KEY (`" + index.Fields[0] + "`)" + index.Extend + ",\n"
		case UniqueIndex:
			sql += "UNIQUE KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ",\n"
		case FulltextIndex:
			sql += "FULLTEXT KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ",\n"
		case KeyIndex:
			sql += "KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ",\n"
		}
	}
	for _, foreign := range table.Foreigns {
		sql += "CONSTRAINT `" + foreign.Name + "` FOREIGN KEY (`" + foreign.Field + "`) REFERENCES `" + foreign.RelationTable + "` (`" + foreign.Field + "`),\n"
	}
	sql = strings.TrimRight(sql, ",\n")
	sql += "\n)ENGINE=" + mysql.preference.Get("ENGINE") + " DEFAULT CHARSET=" + mysql.preference.Get("CHARSET") + ""
	//fmt.Println("add table", sql)
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) RemoveTable(table *Table) error {
	sql := "DROP TABLE `" + table.Name + "` "
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) AddColumn(table *Table, column *Column) error {
	sql := "ALTER TABLE `" + table.Name + "` ADD COLUMN `" + column.Field + "` " + string(column.Type)
	if column.Length > 0 {
		sql += "(" + utils.ToString(column.Length) + ")"
	}
	if column.Flag&UnsignedFlag > 0 {
		sql += " UNSIGNED"
	}
	if column.Flag&ZerofillFlag > 0 {
		sql += " zerofill"
	}
	if column.Flag&AllowNullFlag > 0 {
		sql += " NULL"
	} else {
		sql += " NOT NULL"
	}
	if column.Flag&AutoFlag > 0 {
		sql += " AUTO_INCREMENT"
	}
	if column.Default != "" {
		sql += " DEFAULT " + column.Default + ""
	}
	if column.Comment != "" {
		sql += " COMMENT " + column.Comment + ""
	}
	i := -1
	for index, col := range table.Columns {
		if col.Field == column.Field {
			i = index
			break
		}
	}
	if i >= 1 {
		sql += " AFTER `" + table.Columns[i-1].Field + "`"
	} else if i == 0 {
		sql += " BEFORE `" + table.Columns[i+1].Field + "`"
	}
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) RemoveColumn(table *Table, column *Column) error {
	sql := "ALTER TABLE `" + table.Name + "` DROP `" + column.Field + "`"
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) ChangeColumn(table *Table, column *Column) error {
	sql := "ALTER TABLE `" + table.Name + "` MODIFY `" + column.Field + "` " + string(column.Type)
	if column.Length > 0 {
		sql += "(" + utils.ToString(column.Length) + ")"
	}
	if column.Flag&UnsignedFlag > 0 {
		sql += " UNSIGNED"
	}
	if column.Flag&ZerofillFlag > 0 {
		sql += " zerofill"
	}
	if column.Flag&AllowNullFlag > 0 {
		sql += " NULL"
	} else {
		sql += " NOT NULL"
	}
	if column.Flag&AutoFlag > 0 {
		sql += " AUTO_INCREMENT"
	}
	if column.Default != "" {
		sql += " DEFAULT " + column.Default + ""
	}
	if column.Comment != "" {
		sql += " COMMENT " + column.Comment + ""
	}
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) AddForeign(table *Table, foreign *Foreign) error {
	sql := "ALTER TABLE `" + table.Name + "` ADD "
	sql += "CONSTRAINT `" + foreign.Name + "` FOREIGN KEY (`" + foreign.Field + "`) REFERENCES `" + foreign.RelationTable + "` (`" + foreign.Field + "`),\n"

	_, err := mysql.Exec(sql)
	return err
}
func (mysql *mysqlLogic) RemoveForeign(table *Table, foreign *Foreign) error {
	sql := "ALTER TABLE `" + table.Name + "` DROP FOREIGN KEY (`" + foreign.Field + "`)"
	_, err := mysql.Exec(sql)
	return err
}
func (mysql *mysqlLogic) AddIndex(table *Table, index *Index) error {
	sql := "ALTER TABLE `" + table.Name + "` ADD "
	switch index.Type {
	case PrimaryIndex:
		sql += "PRIMARY KEY (`" + index.Fields[0] + "`)" + index.Extend + ""
	case UniqueIndex:
		sql += "UNIQUE KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ""
	case FulltextIndex:
		sql += "FULLTEXT KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ""
	case KeyIndex:
		sql += "KEY `" + index.Name + "` (`" + strings.Join(index.Fields, "`,`") + "`)" + index.Extend + ""
	}
	_, err := mysql.Exec(sql)
	return err
}
func (mysql *mysqlLogic) RemoveIndex(table *Table, index *Index) error {
	sql := "ALTER TABLE `" + table.Name + "` DROP "
	switch index.Type {
	case PrimaryIndex:
		sql += "PRIMARY KEY"
	case UniqueIndex:
		sql += "UNIQUE KEY `" + index.Name + "`"
	case FulltextIndex:
		sql += "FULLTEXT KEY `" + index.Name + "`"
	case KeyIndex:
		sql += "KEY `" + index.Name + "`"
	}
	_, err := mysql.Exec(sql)
	return err
}

func (mysql *mysqlLogic) AddRecords(table *Table, records [][]interface{}) error {
	args := make([]interface{}, 0, len(records)*len(records[0]))
	valueStr := ""
	l := len(records)

	fieldStr, valueStrTemplate, err := mysql.getInsertField(table)
	for i := 0; i < l; i++ {
		valueStr += valueStrTemplate
		args = append(args, records[i]...)
		if i+1 < l {
			valueStr += ","
		}
	}
	s := "INSERT INTO %s %s VALUES %s"
	_, err = mysql.Exec(fmt.Sprintf(s, table.Name, fieldStr, valueStr), args...)
	return err
}
func (mysql *mysqlLogic) ClearRecords(table *Table) error {
	sql := "TRUNCATE TABLE `" + table.Name + "`"
	_, err := mysql.Exec(sql)
	return err
}
func (mysql *mysqlLogic) AllRecords(table *Table) ([][]interface{}, error) {
	sql := "SELECT * FROM `" + table.Name + "`"
	rows, err := mysql.Query(sql)
	if err != nil {
		return nil, err
	}
	fields, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	l := len(fields)
	records := [][]interface{}{}
	for rows.Next() {
		newDest := make([]interface{}, l)
		newI := make([]interface{}, l)
		for j := 0; j < l; j++ {
			newDest[j] = &newI[j]
		}

		err := rows.Scan(newDest...)
		if err != nil {
			return nil, err
		}
		var record []interface{}
		for index := range newDest {
			record = append(record, table.Columns[index].Format(newI[index]))
		}
		records = append(records, record)
	}
	return records, nil
}
func (mysql *mysqlLogic) ReplaceRecords(table *Table, records [][]interface{}) error {
	l := len(records)
	fieldStr, valueStrTemplate, err := mysql.getInsertField(table)
	for i := 0; i < l; i++ {
		s := "INSERT INTO %s %s VALUES %s ON DUPLICATE KEY UPDATE %s"
		setSql, update, err := mysql.getSet(table, records[i])
		if err != nil {
			return err
		}
		fmt.Println(fmt.Sprintf(s, table.Name, fieldStr, valueStrTemplate, setSql), records[i], update)
		if _, err = mysql.Exec(fmt.Sprintf(s, table.Name, fieldStr, valueStrTemplate, setSql), append(records[i], update...)...); err != nil {
			return err
		}
	}

	return err
}
func parseSchema(schema string) *Table {
	schema = strings.TrimSpace(schema)
	lines := strings.Split(schema, "\n")
	table := &Table{
		Columns:  []*Column{},
		Indexs:   []*Index{},
		Foreigns: []*Foreign{},
	}

	for i := 1; i < len(lines)-1; i++ {
		line := strings.TrimSpace(lines[i])
		if len(line) == 0 {
			continue
		}
		line = strings.TrimRight(line, ",")

		//`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
		//`name` text NOT NULL,
		columnMatch := columnReg.FindStringSubmatch(line)
		if len(columnMatch) > 0 {
			column := &Column{
				Field:  columnMatch[1],
				Type:   ColumnType(columnMatch[2]),
				Length: utils.ToInt(strings.Trim(strings.Trim(columnMatch[3], "("), ")")),
			}
			defaulIndex := strings.Index(line, "DEFAULT ")
			if defaulIndex > 0 {
				defaultStr := line[defaulIndex+8:]
				var defau int
				if defaultStr[0:1] == "'" {
					defaultStr = defaultStr[1:]
					defau = strings.Index(defaultStr, "'")
					column.Default = "'" + strings.Trim(defaultStr[:defau], "'") + "'"
				} else {
					defau = strings.Index(defaultStr, " ")
					if defau >= 0 {
						column.Default = defaultStr[:defau]
					} else {
						column.Default = defaultStr
					}
				}
			}
			commentIndex := strings.Index(line, "COMMENT ")
			if commentIndex > 0 {
				column.Comment = line[commentIndex+8:]
			}

			if strings.Contains(line, "unsigned") {
				column.Flag |= UnsignedFlag
			}
			if strings.Contains(line, "NOT NULL") {
				//column.Flag |= AllowNullFlag
			} else if strings.Contains(line, "NULL") {
				column.Flag |= AllowNullFlag
			} else if column.Type == TEXT {
				column.Flag |= AllowNullFlag
			}
			if strings.Contains(line, "zerofill") {
				column.Flag |= ZerofillFlag
			}
			if strings.Contains(line, "AUTO_INCREMENT") {
				column.Flag |= AutoFlag
			}
			table.Columns = append(table.Columns, column)
			continue
		}
		if strings.HasPrefix(line, string(PrimaryIndex)) {
			index := &Index{}
			index.Type = PrimaryIndex
			index.Name = "PRIMARY KEY"
			p := strings.Trim(strings.Replace(line, "PRIMARY KEY ", "", 1), "(`")
			pp := strings.Index(p, "`")
			field := p[0:pp]
			index.Fields = []string{field}
			index.Extend = strings.Trim(p[pp+1:], ")")
			table.Indexs = append(table.Indexs, index)
			continue
		}
		// UNIQUE KEY `idx_a` (`a`) USING HASH COMMENT '注释',
		// FULLTEXT KEY `c` (`c`)
		// PRIMARY KEY (`d`) USING BTREE
		// KEY `idx_e` (`e`),
		indexMatches := indexReg.FindStringSubmatch(line)
		if len(indexMatches) > 0 {
			index := &Index{}
			index.Type = IndexType(indexMatches[1] + "KEY")
			index.Name = strings.TrimRight(strings.TrimLeft(indexMatches[2], " `"), "`")
			fields := strings.Split(indexMatches[3], ",")
			for _, field := range fields {
				field = strings.TrimSpace(field)
				field = strings.Trim(field, "`")
				index.Fields = append(index.Fields, field)
			}
			table.Indexs = append(table.Indexs, index)
			continue
		}

		// CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`)
		foreignMatches := foreignKeyReg.FindStringSubmatch(line)
		if len(foreignMatches) > 0 {
			foreign := &Foreign{}
			foreign.Name = foreignMatches[1]
			foreign.Field = foreignMatches[2]
			foreign.RelationTable = foreignMatches[3]
			foreign.RelationField = foreignMatches[4]
			table.Foreigns = append(table.Foreigns, foreign)
			continue
		}

		autoMatches := autoReg.FindStringSubmatch(line)
		if len(autoMatches) > 0 {
			table.Auto = utils.ToInt(autoMatches[1])
		}
	}
	return table
}

func (mysql *mysqlLogic) getSet(table *Table, data []interface{}) (string, []interface{}, error) {
	var buf bytes.Buffer
	l := len(table.Columns)
	update := []interface{}{}
	for k, v := range table.Columns {
		if v.Flag&AutoFlag > 0 {
			continue
		}
		update = append(update, data[k])
		buf.WriteString("`" + v.Field + "`=")
		buf.WriteString(" ? ")
		if k+1 < l {
			buf.WriteString(",")
		}
	}
	return buf.String(), update, nil
}

func (mysql *mysqlLogic) getInsertField(table *Table) (string, string, error) {
	var field bytes.Buffer
	var value bytes.Buffer
	l := len(table.Columns)
	field.WriteString("(")
	value.WriteString("(")
	for k, v := range table.Columns {
		field.WriteString("`" + v.Field + "`")
		value.WriteString("?")
		if k+1 < l {
			field.WriteString(",")
			value.WriteString(",")
		}
	}
	field.WriteString(")")
	value.WriteString(")")
	return field.String(), value.String(), nil
}

var columnReg = regexp.MustCompile("`(.+)` ([a-zA-Z]+)(\\([0-9]*\\))?")

var indexReg = regexp.MustCompile("^([A-Z]+\\s)?KEY( `.+`)? \\((.+)\\)")

var foreignKeyReg = regexp.MustCompile("^CONSTRAINT `(.+)` FOREIGN KEY \\(`(.+)`\\) REFERENCES `(.+)` \\(`(.+)`\\)")

var autoReg = regexp.MustCompile(`\sAUTO_INCREMENT=([1-9]\d*)\s`)
